﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using FTchina.DAL;
using FTchina.Library;
using System.IO;
using System.Web.UI.WebControls;
namespace FTchina.OfficeHelper
{
    /// <summary>
    ///Web Control 导出到 Excel
    /// </summary>
    public class ToExcel
    {
        public ToExcel()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// Control导出到Excel
        /// </summary>
        /// <param name="ctl"></param>
        public static void ControlToExcel(System.Web.UI.Control ctl)
        {

            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            System.IO.StringWriter sw = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
            ctl.Page.EnableViewState = false;
            ctl.RenderControl(htw);
            string strHtml = sw.ToString().Trim();
            HtmlTableToExcel(strHtml);
        }



        /// <summary>
        /// DataSet导出到Excel,使用GridView2Excel方法
        /// </summary>
        /// <param name="ds"></param>
        public static void DataSetToExcel(DataSet ds)
        {
            GridView2Excel(ds);
        }
        /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="dt"></param>
        public static void DataTableToExcel(DataTable dt)
        {
            string strHtml = GetDataTableHtml(dt);
            HtmlTableToExcel(strHtml);
        }
        /// <summary>
        /// SQL语句导出到Excel，使用GridView2Excel方法
        /// </summary>
        /// <param name="sql"></param>
        public static void DataSetToExcel(string sql)
        {
            GridView2Excel(DBHelper.GetDataSet(sql));
             
        }


        /// <summary>
        /// 使用GridView控件导出ExcelHtml，大数据量稳定
        /// </summary>
        /// <param name="ds"></param>
        public static void GridView2Excel(DataSet ds)
        {
            GridView gvList = new GridView();
            //将分页去除
            gvList.Width = Unit.Percentage(100);
            gvList.AutoGenerateColumns = true;
            gvList.AllowPaging = false;
            gvList.DataSource = ds;
            gvList.DataBind();

            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter writer = new System.Web.UI.HtmlTextWriter(stringWrite);
            gvList.RenderControl(writer);
            HtmlTableToExcel(stringWrite.ToString());
        }


        /// <summary>
        /// 泛型导出到Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public static void IListToExcel<T>(IList<T> list)
        {
            DataSet ds = FTchina.Library.ControlBindHelper.ToDataSet<T>(list);

            GridView2Excel(ds);
        }
        public static string DataSetToHtmlExcel(DataSet ds)
        {
            string data = "";
            //data = ds.DataSetName + "\n";

            foreach (DataTable tb in ds.Tables)
            {
                data += GetDataTableHtml(tb);
            }

            return data;
        }

        /// <summary>
        /// DataTable获取Html，大数据量容易内存溢出
        /// </summary>
        /// <param name="tb"></param>
        /// <returns></returns>
        private static string GetDataTableHtml(DataTable tb)
        {
            string data = "";
            //data += tb.TableName + "\n";
            data += "<table  cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
            //写出列名
            data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
            foreach (DataColumn column in tb.Columns)
            {
                data += "<td>" + column.ColumnName + "</td>";
            }
            data += "</tr>";

            //写出数据
            foreach (DataRow row in tb.Rows)
            {
                data += "<tr>";
                foreach (DataColumn column in tb.Columns)
                {
                    //if (column.ColumnName.Equals("ID"))
                    //    data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
                    //else
                    data += "<td>" + row[column].ToString() + "</td>";
                }
                data += "</tr>";
            }
            data += "</table>";
            return data;
        }

        #region html table 导出到Excel
        public static void HtmlTableToExcel(string table)
        {
            try
            {
                if (1 == 1)
                {
                    HtmlTableToExcel(table, DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + ".xls");
                }
                else
                {
                    table = table.Replace("<table", "<table x:str");//将单元格设置为文本

                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.Buffer = true;
                    HttpContext.Current.Response.Charset = "utf-8";
                    HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + ".xls");
                    // 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
                    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
                    HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 




                    string head = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>" +
                        "<meta http-equiv=Content-Type content='text/html; charset=UTF-8'>";
                    table = head + table;

                    HttpContext.Current.Response.Output.Write(table);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.ApplicationInstance.CompleteRequest();
                    //  HttpContext.Current.Response.End();
                    HttpContext.Current.Response.Close();
                }
            }
            catch (Exception er)
            {
                LogHelper.Debug(er.Message);
            }
            finally
            {
                HttpContext.Current.Response.End();
            }
        }

        /// <summary>
        /// 创建文件后下载
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filename"></param>
        public static void HtmlTableToExcel(string table, string filename)
        {
            try
            {
                //string filename = DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + ".xls";
                table = table.Replace("<table", "<table x:str");//将单元格设置为文本

                string head = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>" +
                    "<meta http-equiv=Content-Type content='text/html; charset=UTF-8'>";
                table = head + table;


                FileHelper.WriteText(FileHelper.GetFullPath("~/tmp/" + filename), table);
               // LogHelper.Debug("生成excel" + DateTime.Now.ToString());

                HttpContext.Current.Response.Redirect("/tmp/" + filename, false);

               // LogHelper.Debug("下载excel" + DateTime.Now.ToString());

               

            }
            catch (Exception er)
            {
                LogHelper.Debug(er.Message);
            }
            finally
            {
                // FileHelper.DeleteFile(FileHelper.GetFullPath("~/tmp/" + filename));


            }
        }

        #endregion




    }
}